SET NOCOUNT ON
/*
 * How to run this trace file
 * First, set the variable here to the location of the source code root 
 */
DECLARE @svnRoot nvarchar(max);
SET @svnRoot = N'E:\temp';

/*
 * Now, run this script!
 * This first section checks to see that a files in that directory are accessible
 */

BEGIN TRY
	DECLARE @SQL nvarchar(max),
		@SQLParms nvarchar(max),
		@fileContents nvarchar(max);

	SELECT @SQL = N'
		SELECT @fileContents = CONVERT(VARCHAR(MAX), val) 
		FROM OPENROWSET (
				BULK ''' + @svnRoot + N'\test\testfile.txt'', SINGLE_BLOB
			) AS testfile(val)',
		@SQLParms = N'@fileContents varchar(max) OUTPUT';
		
	EXEC sp_executesql @SQL, @SQLParms, @fileContents OUTPUT

	PRINT @fileContents;
	PRINT 'Testing Errors (if any) follow:';
END TRY
BEGIN CATCH
	PRINT 'SQL Server has trouble accessing file system';
    PRINT ' Error Number: ' + ISNULL(CAST(ERROR_NUMBER() as nvarchar(max)), 'none');
    PRINT ' Error Severity: ' + ISNULL(CAST(ERROR_SEVERITY() as nvarchar(max)), 'none');
    PRINT ' Error State: ' + ISNULL(CAST(ERROR_STATE() as nvarchar(max)), 'none');
    PRINT ' Error Procedure: ' + ISNULL(CAST(ERROR_PROCEDURE() as nvarchar(max)), 'none');
    PRINT ' Error Line: ' + ISNULL(CAST(ERROR_LINE() as nvarchar(max)), 'none');
    PRINT ' Error Message: ' + ISNULL(CAST(ERROR_MESSAGE() as nvarchar(max)), 'none');
	GOTO end_label
END CATCH

/*
 * Now define the file, tracetype and expected results of the tests
 */
DECLARE @tests TABLE ( testPath nvarchar(100), traceType nvarchar(20), excpectedErrorNumber bigint);
INSERT @tests (testPath, traceType, excpectedErrorNumber)
SELECT '\test\2008 Trace (no end time, no monitorloop).trc', 'FILE', 2627 UNION ALL
SELECT '\test\2008 XML Trace (no end time).xml', 'XMLFILE', NULL UNION ALL
SELECT '\test\2008 Trace.trc', 'FILE', NULL UNION ALL
SELECT '\test\2008 XML Trace.xml', 'XMLFILE', NULL;

/*
 * Time to run the tests
 */
DECLARE @testPath nvarchar(100), 
	@traceType nvarchar(20), 
	@expectedErrorNumber bigint;
DECLARE testCursor CURSOR FOR 
	SELECT testPath, traceType, excpectedErrorNumber 
	FROM @tests;

OPEN testCursor

FETCH NEXT FROM testCursor 
INTO @testPath, @traceType, @expectedErrorNumber;

WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRY
		SET @testPath = @svnRoot + @testPath;
		EXEC dbo.sp_blocked_process_report_viewer
			@Trace = @testPath, 
			@Type = @traceType;
		-- passed.
		IF (@expectedErrorNumber IS NOT NULL)
			PRINT 'While Testing ' + @testPath + 
			    ' as trace type ' + @traceType +
			    ', error number ' + CAST(@expectedErrorNumber as nvarchar(20)) +
			    ' was expected, but the test passed';
    END TRY
    BEGIN CATCH
		IF (@expectedErrorNumber IS NULL 
			OR @expectedErrorNumber <> ERROR_NUMBER())
		BEGIN
			PRINT 'While Testing ' + @testPath + 
			    ' as trace type ' + @traceType +
			    ', error number ' + ISNULL(CAST(@expectedErrorNumber as nvarchar(20)), 'NULL') +
			    ' was expected, but the test failed with error number ' +
			    CAST(ERROR_NUMBER() as nvarchar(max));
			PRINT ' Error Severity: ' + ISNULL(CAST(ERROR_SEVERITY() as nvarchar(max)), 'none');
			PRINT ' Error State: ' + ISNULL(CAST(ERROR_STATE() as nvarchar(max)), 'none');
			PRINT ' Error Procedure: ' + ISNULL(CAST(ERROR_PROCEDURE() as nvarchar(max)), 'none');
			PRINT ' Error Line: ' + ISNULL(CAST(ERROR_LINE() as nvarchar(max)), 'none');
			PRINT ' Error Message: ' + ISNULL(CAST(ERROR_MESSAGE() as nvarchar(max)), 'none');
		END
    END CATCH

	FETCH NEXT FROM testCursor 
	INTO @testPath, @traceType, @expectedErrorNumber;
END 
CLOSE testCursor
DEALLOCATE testCursor

end_label: